package cn.edu.tju.wiki.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

public class DataCon {
	
	public static final String driver = "com.mysql.jdbc.Driver";
	public static final String url = "jdbc:mysql://127.0.0.1/my_wikidb?useUnicode=true&characterEncoding=UTF-8";//UTF-8";
	public static final String user = "root";
	public static final String password = "123";
	private static Connection conn;
	
	public static Integer getNewItemId(String scheme, String sqlStr, String key) throws ClassNotFoundException{
		try {
			if(conn == null){
				Class.forName(driver);
				conn = DriverManager.getConnection(url, user, password);
			}
			Statement stat = null;
			ResultSet rs = null;
			
			try{
				stat = conn.createStatement();
				rs = stat.executeQuery(sqlStr);
				while (rs.next()) {
					return rs.getInt(key);
				}
			}catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				rs.close();
				conn.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
		return null;
	}
	
	public static List<Map<String, Object>> getDataList(String scheme, String sqlStr, String[] keySet) throws ClassNotFoundException{
		try {
			if(conn == null){
				Class.forName(driver);
				conn = DriverManager.getConnection(url, user, password);
			}
			Statement stat = null;
			ResultSet rs = null;
			List<Map<String, Object>> resultList = new ArrayList<Map<String,Object>>();
			try{
				stat = conn.createStatement();
				rs = stat.executeQuery(sqlStr);
				while (rs.next()) {
					Map<String, Object> newItem = new HashMap<String, Object>();
					for (int i = 0; i < keySet.length; i++) {
						if(rs.getObject(keySet[i]) != null){
							newItem.put(keySet[i], rs.getObject(keySet[i]));
						}
					}
					resultList.add(newItem);
				}
				return resultList;
			}catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				rs.close();
//				conn.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
		return null;
	}
	
	public static void insertSQL(String scheme, Map<String, Object> dataMap) throws ClassNotFoundException{
		try {
			if(conn == null){
				Class.forName(driver);
				conn = DriverManager.getConnection(url, user, password);
			}
			Statement stat = null;
			ResultSet rs = null;
			
			StringBuffer sqlStr = new StringBuffer("insert into " + scheme + " (");
			Iterator<String> fieldNames = dataMap.keySet().iterator();
			
			while(fieldNames.hasNext()){
				sqlStr.append((String) fieldNames.next());
				if(fieldNames.hasNext()){
					sqlStr.append(",");
				}
			}
			sqlStr.append(") values (");
			
			fieldNames = dataMap.keySet().iterator();
			while(fieldNames.hasNext()){
				sqlStr.append("'"+(String) dataMap.get(fieldNames.next())+"'");
				if(fieldNames.hasNext()){
					sqlStr.append(",");
				}
			}
			sqlStr.append(")");
			
			System.out.println(sqlStr);
			try{
				stat = conn.createStatement();
				stat.execute(sqlStr.toString());
			}catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				if(rs!=null){
					rs.close();
				}
//				conn.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
	}
	
	public static void updateSql(String scheme, String sqlStr) throws ClassNotFoundException{
		try {
			if(conn == null){
				Class.forName(driver);
				conn = DriverManager.getConnection(url, user, password);
			}
			Statement stat = null;
			try{
				stat = conn.createStatement();
				stat.executeUpdate(sqlStr);
			}catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				conn.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
	}
	
	@SuppressWarnings("finally")
	public static Map<String, String> getParams(String sqlStr) throws ClassNotFoundException{
		Map<String, String> paramMap = new HashMap<String, String>();
		try {
			if(conn == null){
				Class.forName(driver);
				conn = DriverManager.getConnection(url, user, password);
			}
			Statement stat = null;
			ResultSet rs = null;
			
			try{
				stat = conn.createStatement();
				rs = stat.executeQuery(sqlStr);
				while (rs.next()) {
					System.out.println(rs.getString(1) +"------------------" + rs.getString(2));
					paramMap.put(rs.getString(1), rs.getString(2));
				}
			}catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				rs.close();
				conn.close();
				return paramMap;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
		return null;
	}
}
